﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using bdxyit.com.RecruitmentAppSys.DBUtility;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：UniversityListModels
*系统时间：3/25/2012 3:38:38 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:38:38 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class UniversityListDAL : IUniversityListDAL
    {

        private const string SQL_SELECT_UNIVERSITYLIST = " select UniversityID,UniversityName,AreaID "
                                                       + " FROM UniversityLists ";

        private const string SQL_DELETE_UNIVERSITYLIST_BY_UniversityID = " delete from UniversityLists "
                                                                    + " where UniversityID=@UniversityID";

        private const string SQL_UPDATE_UNIVERSITYLIST_BY_UniversityID = " update UniversityLists set "
                                                                       + " UniversityID=@UniversityID,"
                                                                       + " UniversityName=@UniversityName,"
                                                                       + " AreaID=@AreaID"
                                                                       + " where UniversityID=@UniversityID";
        private const string SQL_INSERT_UNIVERSITYLIST = " insert into UniversityLists("
                                                       + " UniversityID,UniversityName,AreaID)"
                                                       + "  values ("
                                                       + " @UniversityID,@UniversityName,@AreaID)";


        private const string FIELD_UniversityID = "@UniversityID";
        private const string FIELD_UniversityName = "@UniversityName";
        private const string FIELD_AreaID = "@AreaID";

        /// <summary>
        /// 查询所有记录
        /// </summary>
        /// <returns></returns>
        public List<UniversityList> SelectUniversityList()
        {
            List<UniversityList> ul = new List<UniversityList>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_UNIVERSITYLIST))
            {
                while (dr.Read())
                {
                    UniversityList u = new UniversityList();
                    u.UniversityID = dr["UniversityID"].ToString();
                    u.UniversityName = dr["UniversityName"].ToString();
                    u.AreaID = dr["AreaID"].ToString();
                    ul.Add(u);
                }
            }
            return ul;
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="UniversityID"></param>
        /// <returns></returns>
        public bool DeleteUniversityListBYUniversityID(string UniversityID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UniversityID, SqlDbType.VarChar, 10);
            parm.Value = UniversityID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_UNIVERSITYLIST_BY_UniversityID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 插入一条记录
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool InsertUniversityList(UniversityList uni)
        {
            bool flag = false;

            SqlParameter[] UniversityListParms = GetUniversityListParameters();
            SqlCommand cmd = new SqlCommand();
            UniversityListParms[0].Value = uni.UniversityID;
            UniversityListParms[1].Value = uni.UniversityName;
            UniversityListParms[2].Value = uni.AreaID;


            foreach (SqlParameter parm in UniversityListParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_UNIVERSITYLIST;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }


        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="uni"></param>
        /// <returns></returns>
        public bool UpdateUniversityListBYUniversityID(UniversityList uni)
        {
            bool flag = false;
            SqlParameter[] UniversityListParms = GetUniversityListParameters();
            SqlCommand cmd = new SqlCommand();
            UniversityListParms[0].Value = uni.UniversityID;
            UniversityListParms[1].Value = uni.UniversityName;
            UniversityListParms[2].Value = uni.AreaID;


            foreach (SqlParameter parm in UniversityListParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_UNIVERSITYLIST_BY_UniversityID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        private SqlParameter[] GetUniversityListParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                	new SqlParameter(FIELD_UniversityID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_UniversityName, SqlDbType.VarChar,50),
					new SqlParameter(FIELD_AreaID, SqlDbType.VarChar,10)};
            return parms;
        }

    }
}